package com.laoxu.breadcrumb.dao;

import com.laoxu.breadcrumb.entity.Category;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @Description:
 * @Author laoxu
 * @Date 2019/10/5 14:51
 **/
public interface CategoryRepository extends JpaRepository<Category,Long> {
    // 查询下一级分类
    List<Category> findByParentId(Long parentId);

    // 递归查询当前分类上级分类（包括当前分类）
    @Query(value="SELECT T2.id, T2.category_name,T2.level, T2.parent_id " +
            "        FROM( SELECT " +
            "        @r AS _id," +
            "        (SELECT @r \\:= parent_id FROM tb_category WHERE id = _id) AS parent_id," +
            "        @l \\:= @l + 1 AS lvl" +
            "        FROM" +
            "        (SELECT @r \\:= :id, @l \\:= 0) vars," +
            "        tb_category h" +
            "        WHERE @r > 0) T1" +
            "        JOIN tb_category T2" +
            "        ON T1._id = T2.id" +
            "        ORDER BY T1.lvl DESC", nativeQuery=true)
    List<Category> findAllById(@Param("id") Long id);

    // 查询某一层级分类
    List<Category> findByLevel(Integer level);
}
